using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// Summary description for PageFunctionsCustom
/// </summary>
public class PageFunctionsCustom
{
    string strErrMessage = "";
    HDDatabase HDD = new HDDatabase();
	public PageFunctionsCustom()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public bool ExecuteCustomFunction(string strOperation, DataSet dsePageInfo, DataSet dseInputInfo)
    {
        bool blnStatus = true;
        string strPagesetRowId = "";
        strPagesetRowId = dsePageInfo.Tables["PageSet"].Rows[0]["af_row_id"].ToString();
        switch (strPagesetRowId.ToLower())
        {
            case "b7dee4c19a31e1e6": // ps_object
                switch (strOperation.ToUpper())
                {
                    case GlobalValues.InsertOperation:
                        //blnStatus = PageSetInsertProcess(dsePageInfo, dseInputInfo);
                        break;
                    case GlobalValues.UpdateOperation:
                        break;
                    case GlobalValues.DeleteOperation:
                        break;
                }
                break;
            //case "ps_company":
            case "c47e60cf5f63f3da": // ps_company_server
                DataTable dtDbServer;
                switch (strOperation.ToUpper())
                {
                    case GlobalValues.InsertOperation:
                        dtDbServer = dseInputInfo.Tables["p_ngcompanyserver"];                        
                        loadCompanyID(dtDbServer);
                        blnStatus = processNewCompany(dtDbServer);
                        EncryptCompanyPassword(dtDbServer);
                        break;
                    case GlobalValues.UpdateOperation:
                        dtDbServer = dseInputInfo.Tables["p_ngcompanyserver"];
                        loadCompanyID(dtDbServer);
                        blnStatus = processExistingCompany(dtDbServer);
                        EncryptCompanyPassword(dtDbServer);
                        break;
                    case GlobalValues.DeleteOperation:
                        break;
                }
                
                break;
        }
        return blnStatus;
    }
    private void EncryptCompanyPassword(DataTable dtDbServer)
    {
        try
        {
            HDDatabase hdd = new HDDatabase();
            foreach (DataRow dr in dtDbServer.Rows)
            {
                string strSql = "update ngcompanyserver set dbpassword='" + security.encrypt(dr["dbpassword"].ToString()) + "' where af_row_id='" + dr["af_row_id"].ToString() + "'";
                SqlCommand sc_user_create = new SqlCommand(strSql);
                hdd.ExecuteCmdObj(sc_user_create);
            }
            hdd = null;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in EncryptCompanyPassword function. " + e.Message);
        }
    }
    private void loadCompanyID(DataTable dtDbServer)
    {
        if (dtDbServer == null) return;
        try
        {
            string orgCompId = "";
            if (dtDbServer.Rows.Count > 0)
            {
                foreach (DataRow dr in dtDbServer.Rows)
                {
                    string compId = dr["company_id"].ToString();
                    DataSet ds = this.HDD.GetDataset("select company_id from ngcompany where af_row_id='" + compId + "'");
                    if (ds != null)
                        if (ds.Tables.Count > 0)
                            if (ds.Tables[0].Rows.Count > 0)
                                orgCompId = ds.Tables[0].Rows[0]["company_id"].ToString();
                    if (orgCompId.Trim().Length > 0) dr["company_id"] = orgCompId;
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in loadCompanyID function. " + e.Message);
        }
    }
    public bool PageSetInsertProcess(DataSet dsePageInfo, DataSet dseInputInfo)
    {
        bool blnPageSetInsertProcess = false;
        string strSql = "";
        string strSql1 = "";
        DataTable  dtObj = dseInputInfo.Tables["p_dobj"];
        foreach (DataRow row in dtObj.Select())
        {
            strSql = "exec generate_table_and_page_controls '" + row["object_id"] + "',1";
        }
        HDDatabase hddb = new HDDatabase ();
        SqlCommand sc = new SqlCommand(strSql1);
        //execute sp to create table & page controls
        sc.CommandText= strSql;
        hddb.ExecuteCmdObj(sc);

        

        blnPageSetInsertProcess = true ;
        return blnPageSetInsertProcess;
    }

    public bool processExistingCompany(DataTable dtDbServer)
    {
        bool blnprocessExistingCompany = false;
        string strConn = "";
        //foreach (DataRow row in dt_parent.Select(strfilter, strsort))
        foreach (DataRow row in dtDbServer.Select())
        {
            strConn = @"Data Source='" + row["server"] + "';Initial Catalog='" + row["dbname"].ToString().Replace("'", "''") + "';User Id='" + row["dbuser"].ToString().Replace("'", "''") + "';Password='" + row["dbpassword"].ToString().Replace("'", "''") + "'";
            HDDatabase hdTemp = new HDDatabase(strConn);
            if (hdTemp.isValidConnection())
            {
                blnprocessExistingCompany = true;
            }
            else
            {
                this.strErrMessage = "Error in connecting the server, database information mismatch";
            }
        }

        return blnprocessExistingCompany;
    }
    public bool processNewCompany(DataTable dtDbServer)
    {
        //strConString = @"Data Source=chen-lptp-001;Initial Catalog=ngmaster;User Id=sa;Password=sa";
        bool blnprocessNewCompany = true;
        string strConn = "";
        //foreach (DataRow row in dt_parent.Select(strfilter, strsort))
        


        foreach (DataRow row in dtDbServer.Select())
        {
            HDDatabase hddb = new HDDatabase();

            //create a default login (admin, update, view) in ngmaster (Saas admin database)
            string strSql = "insert into ngcompanyuser (company_id, user_id, password,af_row_id) values ('" + row["company_id"].ToString().Replace("'", "''") + "','admin','HNJNj91d75XcXzWmDNeQOQ==','650a333596bbf348')";
            SqlCommand sc_user_create = new SqlCommand(strSql );
            hddb.ExecuteCmdObj(sc_user_create);

            strSql = "insert into ngcompanyuser (company_id, user_id, password,af_row_id) values ('" + row["company_id"].ToString().Replace("'", "''") + "','update_user','NP2t5Fd/g0VTxPkf+JW3jL7nTPbCvQkV','ea5ab41af276215a')";
            sc_user_create = new SqlCommand(strSql);
            hddb.ExecuteCmdObj(sc_user_create);

            strSql = "insert into ngcompanyuser (company_id, user_id, password,af_row_id) values ('" + row["company_id"].ToString().Replace("'", "''") + "','view_user','jb8gOuseU4AAExGIkUVc8xUr6HhUKJ7N','322d53926d947f73')";
            sc_user_create = new SqlCommand(strSql);
            hddb.ExecuteCmdObj(sc_user_create);

            strSql = "insert into ngcompanyuser (company_id, user_id, password,af_row_id) values ('" + row["company_id"].ToString().Replace("'", "''") + "','afadmin','s5XGNm3Ubp8ZWDegkOxwzg==','e51e0327224a2eb9')";
            sc_user_create = new SqlCommand(strSql);
            hddb.ExecuteCmdObj(sc_user_create);

            // form a connection string to create new databse using the provided database information for the company
            strConn = @"Data Source=" + row["server"] + ";User Id=" + row["dbuser"] + ";Password=" + security.decrypt(row["dbpassword"].ToString()) + "";
            HDDatabase hdTemp = new HDDatabase(strConn);
            if (hdTemp.isValidConnection())
            {
                strSql = "select * from master..syslogins where name='" + row["company_id"].ToString().Replace("'", "''") + "'";
                if (!hdTemp.isDataRecordExists(strSql))
                {
                    strSql = "select * from master..sysdatabases where name='" + row["dbname"].ToString().Replace("'", "''") + "'";
                    if (!hdTemp.isDataRecordExists(strSql))
                    {
                        SqlCommand sc = new SqlCommand("create database [" + row["dbname"]+ "]");
                        hdTemp.ExecuteCmdObj(sc);
                    }

                    //Database server Login created with company id, password is <<Company_Id>>_123 and default database is ng_<<dbname>>
                    SqlCommand sc1 = new SqlCommand("sp_addlogin '" + row["company_id"].ToString().Replace("'", "''") + "','" + row["company_id"].ToString().Replace("'", "''") + "_123','"
                        + row["dbname"].ToString().Replace("'", "''") + "'");
                    hdTemp.ExecuteCmdObj(sc1);

                    strConn = @"Data Source='" + row["server"] + "';User Id='" + row["dbuser"].ToString().Replace("'", "''") + "';Password='" + security.decrypt(row["dbpassword"].ToString()).Replace("'", "''") + "';Initial Catalog='"
                        + row["dbname"].ToString().Replace("'", "''") + "'";
                    hdTemp = new HDDatabase(strConn);
                    sc1 = new SqlCommand("sp_adduser '" + row["company_id"].ToString().Replace("'", "''") + "','" + row["company_id"].ToString().Replace("'", "''") + "'");
                    hdTemp.ExecuteCmdObj(sc1);

                    sc1 = new SqlCommand("sp_addrolemember 'db_ddladmin','" + row["company_id"].ToString().Replace("'","''") + "'");
                    hdTemp.ExecuteCmdObj(sc1);

                    strConn = @"Data Source='" + row["server"] + "';User Id='" + row["company_id"].ToString().Replace("'", "''") + "';Password='" + row["company_id"].ToString().Replace("'", "''") + "_123';Initial Catalog='"
                        + row["dbname"].ToString().Replace("'", "''") + "'";
                    HDDatabase hdCompDB = new HDDatabase(strConn);
                    ExecuteDBScript(hdCompDB);

                    #region old code for user and user role insertion
                    ////////////create a default user id in company database & assign full access
                    ////////////strSql = "Insert into sy_usr (user_id, last_name, first_name, bu_id, password, theme) values ('admin', '" + row["company_id"].ToString().Replace("'", "''") + "','Administrator','ABC','admin','BlueTheme')";
                    //////////strSql = "Insert into sy_usr (af_row_id, user_id, last_name, first_name, bu_id, password,time_zone) values ('650a333596bbf348', 'admin', '" + row["company_id"].ToString().Replace("'", "''") + "','Administrator','ABC','admin','1059')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);
                    //////////strSql = "Insert into sy_usr_rol (af_row_id, user_id, role_id) values ('377e55cec8d53d7d','admin', 'admin:accessrights')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);

                    ////////////create a default user id in company database & assign update access
                    ////////////strSql = "Insert into sy_usr (user_id, last_name, first_name, bu_id, password, theme) values ('update_user', '" + row["company_id"].ToString().Replace("'", "''") + "','Update','ABC','update_user','BlueTheme')";
                    //////////strSql = "Insert into sy_usr (af_row_id, user_id, last_name, first_name, bu_id, password,time_zone) values ('ea5ab41af276215a','update_user', '" + row["company_id"].ToString().Replace("'", "''") + "','Update','ABC','update_user','1059')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);
                    //////////strSql = "Insert into sy_usr_rol (af_row_id, user_id, role_id) values ('73037fc931493a59','update_user', 'update:accessrights')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);

                    ////////////create a default user id in company database & assign view access

                    ////////////strSql = "Insert into sy_usr (user_id, last_name, first_name, bu_id, password, theme) values ('view_user', '" + row["company_id"].ToString().Replace("'", "''") + "','View','ABC','view_user','BlueTheme')";
                    //////////strSql = "Insert into sy_usr (af_row_id, user_id, last_name, first_name, bu_id, password,time_zone) values ('fea6aaf73245caae','view_user', '" + row["company_id"].ToString().Replace("'", "''") + "','View','ABC','view_user','1059')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);
                    //////////strSql = "Insert into sy_usr_rol (af_row_id, user_id, role_id) values ('322d53926d947f73','view_user', 'view:accessrights')";
                    //////////sc_user_create.CommandText = strSql;
                    //////////hdCompDB.ExecuteCmdObj(sc_user_create);                    
                    #endregion
                    hdCompDB.ExecuteCmdObj(new SqlCommand("update sy_usr set last_name='" + row["company_id"].ToString().Replace("'", "''") + "'"));

                }
            }
            else
            {
                blnprocessNewCompany = false;
                this.strErrMessage = "Error in connecting the server, database information mismatch";
            }
        }

        return blnprocessNewCompany;
    }

    public bool ExecuteDBScript(HDDatabase hdObj)
    {
        bool blnStatus = true;

        string strScriptLocation = "";
        string strAppPath = "";
        strAppPath = System.Web.HttpContext.Current.Request.ApplicationPath;
        strAppPath = System.Web.HttpContext.Current.Server.MapPath(strAppPath);
        if (strAppPath.LastIndexOf(@"\") != -1)
        {
            strAppPath += @"\";
        }
        strScriptLocation = strAppPath + GlobalValues.AppDBScriptLocation;

        PageCommonFunctions PCF = new PageCommonFunctions();
        ArrayList arl = PCF.GetCmdObj(strScriptLocation + @"\ngschema.txt", true);
        blnStatus = hdObj.ExecuteTransaction(arl);
        arl = PCF.GetCmdObj(strScriptLocation + @"\ngschema_data.sql", false);
        blnStatus = hdObj.ExecuteTransaction(arl);

        System.Data.SqlClient.SqlCommand objCommand = PCF.GetHelpCmdObj(strScriptLocation + @"\help_data.txt", true);
        hdObj.ExecuteCmdObj(objCommand);
        return blnStatus;
    }

    public string ErrorMessage
    {
        get
        {
            return strErrMessage;
        }
    }
}
